DROP TABLE SPP.META_PERIODO;
DROP TABLE SPP.META;


CREATE TABLE SPP.META (
       nid_meta             NUMBER(12) NOT NULL,
       nid_indicador        NUMBER(12) NOT NULL,
       fec_inicio           DATE NULL,
       fec_fin              DATE NULL,
       flg_activo           NUMBER(1) NULL
);

ALTER TABLE SPP.META
       ADD  ( PRIMARY KEY (nid_meta) ) ;
       
ALTER TABLE SPP.META
       ADD  ( FOREIGN KEY (nid_indicador)
                             REFERENCES SPP.INDICADOR ) ;       

CREATE TABLE SPP.META_PERIODO (
       nid_meta             NUMBER(12) NOT NULL,
       nid_meta_periodo     NUMBER(12) NOT NULL,
       por_meta             NUMBER(3) NULL,
       por_real_meta        NUMBER(3) NULL,
       txt_periodo          VARCHAR2(20) NULL,
       por_positivo         NUMBER(3) NULL,
       por_negativo         NUMBER(3) NULL
);

ALTER TABLE SPP.META_PERIODO
       ADD  ( PRIMARY KEY (nid_meta_periodo) ) ;
       
ALTER TABLE SPP.META_PERIODO
       ADD  ( FOREIGN KEY (nid_meta)
                             REFERENCES SPP.META ) ;         

DROP SEQUENCE SPP.SQ_META_PERIODO;
DROP SEQUENCE SPP.SQ_META;


CREATE SEQUENCE SPP.SQ_META MINVALUE 1 START WITH 1 INCREMENT BY 1 NOCACHE;
CREATE SEQUENCE SPP.SQ_META_PERIODO MINVALUE 1 START WITH 1 INCREMENT BY 1 NOCACHE;

CREATE OR REPLACE TRIGGER SPP.META_BI
  BEFORE INSERT
  ON SPP.META
  FOR EACH ROW
  WHEN (new.NID_META is null)
DECLARE
  v_id SPP.META.NID_META%TYPE;
BEGIN
  SELECT SPP.SQ_META.nextval INTO v_id FROM DUAL;
  :new.NID_META := v_id;
END META_BI;

CREATE OR REPLACE TRIGGER SPP.META_PERIODO_BI
  BEFORE INSERT
  ON SPP.META_PERIODO
  FOR EACH ROW
  WHEN (new.NID_META_PERIODO is null)
DECLARE
  v_id SPP.META_PERIODO.NID_META_PERIODO%TYPE;
BEGIN
  SELECT SPP.SQ_META_PERIODO.nextval INTO v_id FROM DUAL;
  :new.NID_META_PERIODO := v_id;
END META_PERIODO_BI;



